﻿using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;

namespace JunQuanPrinter
{
    class CEpplus
    {

        /// <summary>
        /// 导入Excel
        /// </summary>
        /// <param name="worksheet"></param>
        /// <returns></returns>
        public static DataTable WorksheetToTable(ExcelWorksheet worksheet)
        {
            //获取worksheet的行数
            int rows = worksheet.Dimension.End.Row;
            //获取worksheet的列数
            int cols = worksheet.Dimension.End.Column;
            DataTable dt = new DataTable(worksheet.Name);
            DataRow dr = null;
           
            for (int i = 1; i <= rows; i++)
            {
                if (i >= 1)
                {
                    dr = dt.Rows.Add();

                }
                for (int j = 1; j <= cols; j++)
                {
                    try
                    {
                        if (i == 1)
                        {
                            dt.Columns.Add(j.ToString());
                        }
                        if (worksheet.Cells[i, j].Value != null)
                        {
                            dr[j - 1] = worksheet.Cells[i, j].Value.ToString();
                        }
                    }
                    catch (Exception ex)
                    {

                        throw ex;
                    }
                }
            }
            return dt;
        }


        /// <summary>
        /// 导入Excel
        /// </summary>
        /// <param name="sExcelName"></param>
        /// <returns></returns>
        public static DataTable WorksheetToTable(String sExcelName)
        {
            FileStream fs = null;
            try
            {
                fs = new FileStream(sExcelName, FileMode.Open);
                using (ExcelPackage package = new ExcelPackage(fs))
                {
                    DataTable dt = WorksheetToTable(package.Workbook.Worksheets[1]);
                    return dt;
                }
            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally
            {
                if(fs != null)
                {
                    fs.Close();
                }
            }
        }
    }
}
